package com.is.control;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import com.is.dbconn.DBConnect;
import com.is.model.HomeworkTime;
import com.is.util.Pagination;
public class HomeworkTimeControl {
    public static Pagination pageInstance = null;


		//插入新增听写纪录的id号
		public static int insertHomeworkTime(HomeworkTime dt){
			DBConnect dbConnect = new DBConnect();
			String sql = "insert into homework_time (courseClassId,content,time) values(?,?,?)";
			dbConnect.prepareStatement(sql);
			ResultSet rSet = null;
			int tid=0;
			try {
				setValue(dt, dbConnect);
				if(dbConnect.executeUpdate()){
					 rSet = dbConnect.getPreparedStatement().getGeneratedKeys();//返回插入数据后新增行的id
						if(rSet.next()){
							tid= rSet.getInt(1);
						}
				}
			} catch (Exception e) {
				// TODO: handle exception
			    e.printStackTrace();
			    System.out.println("insertDictaionTime ERROR");
			}finally{
				dbConnect.close();
			}
			return tid ;
		}
		
		public static HomeworkTime getHomeworkTimebyId(int timeId) {
		        HomeworkTime homework = new HomeworkTime();
			DBConnect dbc = new DBConnect();
			String sql = "select * from homework_time where id="+timeId;
			ResultSet rSet=null;
			dbc.prepareStatement(sql);
			try {
				rSet = dbc.executeQuery();
				if(rSet.next()){
					homework.setContent(rSet.getString("content"));
					homework.setCourseClassId(rSet.getInt("courseClassId"));
					homework.setId(rSet.getInt("id"));
					homework.setTime(rSet.getString("time"));
				}
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
				System.out.println("getDictationTimebyId ERROR");
			}finally{
				dbc.close();
			}
			return homework;
		}
		
		/**
		 * 
		 * @param courseClassId
		 * @param stuyear
		 * @return
		 */
		public static int getCount(int courseClassId,int stuyear,String time1,String time2){
		    DBConnect dbConnect = new DBConnect();
		    String sTime = ParamStuyearControl.getStuyear(stuyear).getStartTime();
		    if(time1!=null&&!time1.equals("")){
		    	sTime = time1;
		    }
		    String eTime = ParamStuyearControl.getStuyear(stuyear).getEndTime();
		    if(time2!=null&&!time2.equals("")){
		    	eTime = time2;
		    }
		    int  count = 0;
		    String sql = "select count(id) from homework_time where time between '"+sTime+"' and '"+eTime+"' and courseClassId = '"+courseClassId+"'";
		    dbConnect.prepareStatement(sql);
		    ResultSet rSet = null;
		    try {
			rSet = dbConnect.executeQuery();
			if(rSet.next()){
			    count = rSet.getInt(1);
			}
		    } catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getCountByCourseIdAndStuyear ERROR");
		    }finally{
			dbConnect.close();
		    }
		    return count;
		}
		
		public static void setValue(HomeworkTime homework,DBConnect dbc){
			//调用此方法需注意字段顺序(与sql语句保持一致)
			int index=1;
			try{
			dbc.setInt(index++, homework.getCourseClassId());
			dbc.setString(index++,homework.getContent());
			dbc.setString(index++, homework.getTime());
			
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("setValue方法出错");
		}
		}
		
		public static Collection<Object> getAllHomeworkTime( int courseClassId,String time, int page, int rowsPerPage) throws SQLException,Exception{
			time = ((time==null)||time.equals(""))?"%":time;
			String sql = "select * from homework_time where courseCLassId ='"+courseClassId+"' and time like '%"+time+"%'";
			System.out.println(sql);
			pageInstance = new HomeworkTimeControlPage(sql, rowsPerPage);
			try {
			     return pageInstance.getPage(page);
			}catch (Exception e) {
				e.printStackTrace();
			}
			return null;
		}
	}
class HomeworkTimeControlPage extends Pagination{
	public HomeworkTimeControlPage(String sql, int rowsPerPage){
		super(sql, rowsPerPage, 1);
	}
	public Collection<HomeworkTime> packResultSet(ResultSet rs){
		Collection<HomeworkTime> dts = new ArrayList<HomeworkTime>();
		try{
			while(rs.next()){
			         HomeworkTime dt = new HomeworkTime();
				dt.setContent(rs.getString("content"));
				dt.setCourseClassId(rs.getInt("courseClassId"));
				dt.setTime(rs.getString("time"));
				dt.setId(rs.getInt("id"));
				dts.add(dt);
			}
			return dts;
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("调用packResultSet失败");
			return null;
		}
	}
}



